﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;



/// <summary>
/// Excel 类文件包含两个主要类，实现从excel到数据库和从数据库到excel的双向操作
/// </summary>


public class ExcelToSQL  //实现从excel导入到数据库的类
{
    public SqlConnection sqlcon = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");//yandongxu.test.dbo

    public SqlCommand sqlcom;          //创建SQL命令对象  

    public ExcelToSQL()
    {
        if (sqlcon.State.ToString() == "Open")
            sqlcon.Close();
    }
    public int ImportSql(string excelPath, string tableName)  //导入的Excel的路径，数据库里的表名  
    {
        if (!TableExist(tableName)) //表名是否存在  
            return (int)ImportState.tableNameError;

        System.Data.DataTable dt = ExcelToDataTable(excelPath);
        if (dt == null)
        {
            return (int)ImportState.excelFormatError;
        }

        ArrayList tableField = GetTableField(tableName);
        //表格的列名称  

        //string columnName = "ID,"; //Excel里的列名，增加一个ID列,如果ID自动递增则不需要增加ID列，只需要columnName=“”就可以了。  
        string columnName = "";
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            columnName += dt.Columns[i].ColumnName + ",";
            string currentColumn = dt.Columns[i].ToString().ToUpper(); //当前列名  
            for (int j = 0; j < tableField.Count; j++)
            {
                if (tableField[j].ToString().ToUpper() == dt.Columns[i].ToString().ToUpper())
                    break;   //跳出本层和上一层循环，continue是跳出本层循环，如果用continue，会继续执行j++  
                //Excel里的字段必须在Sql中都有  
                if ((tableField[j].ToString().ToUpper() != dt.Columns[i].ToString().ToUpper()) && j == tableField.Count - 1)
                    return (int)ImportState.fieldMatchError;
            }
        }
        int m = columnName.LastIndexOf(',');
        columnName = columnName.Remove(m);  //移除最后一个逗号  

        sqlcom = new SqlCommand();
        sqlcom.Connection = sqlcon;
        sqlcon.Open();
        sqlcom.CommandType = CommandType.Text;

        for (int h = 0; h < dt.Rows.Count; h++)
        {
            //string value = "'" + System.Guid.NewGuid().ToString() + "'" + ","; //如果ID自动递增ID列不需要增加了，那么value的初始值只需要value=“”就可以了。  

            string value = "";
            if (dt.Rows[h][0].ToString().Trim() != "")
            {

                for (int k = 0; k < dt.Columns.Count; k++) //根据列名得到值  
                {

                    value += "'" + dt.Rows[h][k].ToString() + "'" + ",";

                }
                value = value.Remove(0, 1);
                int n = value.LastIndexOf(',');
                value = value.Remove(n);    //移除最后一个逗号  
                n = value.LastIndexOf("'");
                value = value.Remove(n);

                try
                {
                    string sql = "insert into " + tableName + "(" + columnName + ") values('" + value + "')";
                    sqlcom.CommandText = sql;
                    //Alert(sql);  
                    //TextBox1.Text = sql;
                    string sss = sqlcom.ExecuteNonQuery().ToString();
                }
                catch (Exception err)
                {
                    string erroe = err.Message;
                    return (int)ImportState.dataTypeError;
                }
            }
        }
        sqlcon.Close();
        sqlcom.Dispose();

        return (int)ImportState.right;

    }
    public System.Data.DataTable ExcelToDataTable(string excelPath)  //把Excel里的数据转换为DataTable，并返回DataTable  
    {
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'";
        System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
        string strCom = "SELECT * FROM [Sheet1$]";
        System.Data.DataTable dt;
        try
        {
            Conn.Open();
            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "[Sheet1$]");
            Conn.Close();
            dt = ds.Tables[0];
        }
        catch (Exception err)
        {
            return null;
        }
        return dt;
    }
    public bool TableExist(string tableName) //查看数据库里是否有此表名  
    {
        sqlcom = new SqlCommand();
        sqlcom.Connection = sqlcon;
        sqlcom.CommandType = CommandType.Text;
        try
        {
            sqlcon.Open();
            string sql = "select name from sysobjects where type='u'";
            sqlcom.CommandText = sql;
            SqlDataReader sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                if (sqldr.GetString(0).ToUpper() == tableName.ToUpper())
                    return true;
            }
        }
        catch { return false; }
        finally
        {
            sqlcon.Close();
        }
        return false;
    }
    public ArrayList GetTableField(string tableName)  //得到数据库某一个表中的所有字段  
    {
        ArrayList al = new ArrayList();
        sqlcom = new SqlCommand();
        sqlcom.Connection = sqlcon;
        sqlcom.CommandType = CommandType.Text;
        try
        {
            sqlcon.Open();
            string sql = "SELECT b.name FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id WHERE (a.name = '" + tableName + "')";
            sqlcom.CommandText = sql;
            SqlDataReader sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                al.Add(sqldr.GetString(0));
            }
        }
        finally
        {
            sqlcon.Close();
        }
        return al; //返回的是表中的字段  
    }
    public enum ImportState
    {
        right = 1, //成功  
        tableNameError = 2,//表名不存在  
        fieldMatchError = 3,//excel里的字段和数据库表里的字段不匹配  
        dataTypeError = 4, //转换数据类型时发生错误  
        excelFormatError = 5,//Excel格式不能读取  
    }
    public void Alert(string str)
    {
        HttpContext.Current.Response.Write("<script language='javascript'>alert('" + str + "');</script>");
    }
}


public class DataTableToExcel  //实现从数据库到excel的类
{
    private SqlConnection sc = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
    private SqlCommand cmd;
    public DataTableToExcel()
    {
        // 
        //TODO: 在此处添加构造函数逻辑 
        // 
    }
    ///导出到Excel的主程序 
    public static void DataToExcel(System.Data.DataTable dtCompany, HttpResponse pageResponse, string sField_Desc)
    {//使用写数据流的方式更具用灵活性，更容易控制。 
        HttpResponse resp;
        resp = pageResponse;
        resp.Charset = "GB2312";
        //resp.Buffer = true;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
        DataRow[] myRow = dtCompany.Select("");
        string colHeaders = "";
        string ls_item = "";
        string[] array = sField_Desc.Split(',');
   
        /*
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.ApplicationClass m_objExcel = new Microsoft.Office.Interop.Excel.ApplicationClass(); 
            m_objExcel.Visible = false;
            Microsoft.Office.Interop.Excel.Workbooks m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook m_objBook = (Microsoft.Office.Interop.Excel.Workbook)(m_objBooks.Add(miss));
            Microsoft.Office.Interop.Excel.Worksheet m_ojbSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;
            ((Microsoft.Office.Interop.Excel.Range)(m_ojbSheet.Columns[1, Type.Missing])).NumberFormatLocal="@";
            能打开excel
        */
 
        for (int i = 0; i < array.Length; i++)
        {
            if (i == (array.Length - 1))//最后一列，加\n,换行 
            {
                colHeaders += array[i].ToString().Trim() + "\n";
            }
            else
            {
                colHeaders += array[i].ToString().Trim() + "\t";
            }
        }
        resp.Write(colHeaders);



        //把数据写入Excel中 
        foreach (DataRow row in myRow)
        {
            //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据      
            for (int i = 0; i < array.Length; i++)
            {
                if (i == (array.Length - 1))//最后一列，判断最后一行的值，根据不同的值写入不//同的数据，并且加 \n 
                {
                    
                    ls_item += row[i].ToString().Trim() + "\n";
                }
                else
                {
                    string stemp = row[i].ToString().Trim();
                    ls_item += row[i].ToString().Trim() + "\t";
                }
            }
            resp.Write(ls_item);
            ls_item = "";
        }
        resp.Flush();
        resp.Close();
    }

    public System.Data.DataTable GetCompanyAll(string sql)
    {
        if (sc.State == ConnectionState.Closed)
        {
            sc.Open();
        }
        cmd = new SqlCommand(sql, sc);
        cmd.CommandType = CommandType.Text;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        int rn = da.Fill(ds);
        if (rn > 0)
        {
            return ds.Tables[0];
        }
        return null;
    }

}